package com.wy.tingshu.repository;

import com.wy.tingshu.data_object.TidToCountDO;
import com.wy.tingshu.util.DBUtil;
import com.wy.tingshu.util.Log;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 操作relation表
 **/
public class RelationRepo {
    //查询音频对应的被几个专辑应用（即查询音频的关联数量）
    @SneakyThrows
    public List<TidToCountDO> selectCountByTidListGroupByTidList(List<Integer> tidList) {
        //聚合查询
    String sqlFormat="select tid, count(*) as ref_count from relations where tid in (%s) group by tid order by tid";
    //用StringBuilder拼接:tidList [1,2,3,4]->"1, 2, 3, 4"
    StringBuilder sb=new StringBuilder();
    for (Integer tid:tidList){
        sb.append(tid).append(", ");//这里不带空格会有500错误
    }
    sb.delete(sb.length()-2,sb.length());
    String sql=String.format(sqlFormat,sb.toString());//这样有多少个tid就拼接多少个[stringbuilder格式变为字符串格式]
    List<TidToCountDO> list=new ArrayList<>();
    try(Connection c= DBUtil.connection()){
        try (PreparedStatement ps=c.prepareStatement(sql)){
            Log.println("执行SQL"+ps);
            try(ResultSet rs=ps.executeQuery()){
                while (rs.next()){
                    TidToCountDO tidToCountDO=new TidToCountDO(
                            rs.getInt("tid"),//音频id
                            rs.getInt("ref_count")//音频被专辑引用的次数
                    );
                    list.add(tidToCountDO);
                }
            }
        }
    }
    return list;
    }
    //查询音频列表
    //通过aid(专辑主键)筛选出关系表中的tid(音频表主键)
    @SneakyThrows
    public List<Integer> selectListByAid(int aid) {
        String sql="select tid from relations where aid=? order by rid";//rid:关系表的主键
        List<Integer> tidList=new ArrayList<>();
        try (Connection c=DBUtil.connection()){
            try (PreparedStatement ps=c.prepareStatement(sql)){
                ps.setInt(1,aid);
                Log.println("执行SQL:"+sql);
                try (ResultSet rs= ps.executeQuery()){
                    while (rs.next()){
                        tidList.add(rs.getInt("tid"));
                    }
                }
            }
        }
        return tidList;
    }
}
